package com.situ.mvc.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.support.JdbcUtils;

import com.situ.mvc.dao.IRoleDao;
import com.situ.mvc.entity.Department;
import com.situ.mvc.entity.Role;
import com.situ.mvc.entity.User;
import com.situ.mvc.util.ModelConvertUtil;
import com.situ.mvc.util.UserJDBCUtil;

public class RoleDaoImpl implements IRoleDao {

	@Override
	public int totalCount() {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		int count = 0;
		try {
			connection = UserJDBCUtil.getConnection();
			String sql = "SELECT count(u.id) FROM `user`"
					+ " AS u INNER JOIN department AS d ON"
					+ " u.department_id = d.id INNER JOIN"
					+ " user_role AS ur ON u.id = ur.user_id "
					+ "INNER JOIN role as r ON ur.role_id = r.id;";
			preparedStatement = connection.prepareStatement(sql);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				count = resultSet.getInt(1);
			}
			System.out.println(count);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			UserJDBCUtil.close(connection, preparedStatement, resultSet);
		}
		return count;
	}

	@Override
	public List<Map<String, Object>> findList(int offset, Integer pageSize) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		List<Map<String, Object>> list = new ArrayList<>();
		try {
			connection = UserJDBCUtil.getConnection();
			String sql = "SELECT u.id as 'u_id',"
					+ "u.`name` AS 'u_name', u.`password` AS 'u_password'"
					+ ",u.email AS 'u_email', u.age AS 'u_age',"
					+ " r.`name` AS 'r_name',r.sn AS 'r_sn', d.`name` AS 'd_name'"
					+ " FROM `user` AS u LEFT JOIN department AS d"
					+ " ON u.department_id = d.id LEFT JOIN user_role AS ur "
					+ "ON u.id = ur.user_id LEFT JOIN role as r ON ur.role_id = r.id limit ?,?;";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, offset);
			preparedStatement.setInt(2, pageSize);
			System.out.println(preparedStatement);
			resultSet = preparedStatement.executeQuery();
			list = ModelConvertUtil.converToList(resultSet);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			UserJDBCUtil.close(connection, preparedStatement, resultSet);
		}
		return list;
	}

	@Override
	public List<Department> selectDepartment() {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		ArrayList<Department> list = new ArrayList<>();
		try {
			connection = UserJDBCUtil.getConnection();
			String sql = "select id,name from department;";
			preparedStatement = connection.prepareStatement(sql);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				String name = resultSet.getString("name");
				Department department = new Department(id, name);
				list.add(department);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			UserJDBCUtil.close(connection, preparedStatement, resultSet);
		}
		return list;
	}

	@Override
	public boolean addUser(User user) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		int count = 0;
		boolean result = false;
		try {
			connection = UserJDBCUtil.getConnection();
			String sql = "insert into user(name,password,email,department_id,age) values(?,?,?,?,?)";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, user.getName());
			preparedStatement.setString(2, user.getPassword());
			preparedStatement.setString(3, user.getEmail());
			preparedStatement.setInt(4, user.getDepartment_id());
			preparedStatement.setInt(5, user.getAge());
			System.out.println(preparedStatement);
			count = preparedStatement.executeUpdate();
			if (count > 0) {
				result = true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			UserJDBCUtil.close(connection, preparedStatement, null);
		}
		return result;
	}

	@Override
	public List<Role> selectRole() {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		ArrayList<Role> list = new ArrayList<>();
		try {
			connection = UserJDBCUtil.getConnection();
			String sql = "select id,name from role;";
			preparedStatement = connection.prepareStatement(sql);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				String name = resultSet.getString("name");
				Role role = new Role(id, name);
				list.add(role);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			UserJDBCUtil.close(connection, preparedStatement, resultSet);
		}
		return list;
	}

	@Override
	public boolean addOnlyRole(Integer userId, Integer roleId) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		boolean result = false;
		int count = 0;
		try {
			connection = UserJDBCUtil.getConnection();
			String sql = "insert into user_role(user_id,role_id) values(?,?);";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, userId);
			preparedStatement.setInt(2, roleId);
			count = preparedStatement.executeUpdate();
			if (count > 0) {
				result = true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	@Override
	public boolean deleteById(Integer id) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		boolean result = false;
		int count = 0;
		try {
			connection = UserJDBCUtil.getConnection();
			String sql = "delete from user where id = ?;";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, id);
			count = preparedStatement.executeUpdate();
			if (count > 0) {
				result = result;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			UserJDBCUtil.close(connection, preparedStatement, null);
		}
		return result;
	}

}
